﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace ZHDJ.Data.Models.Center
{
    /// <summary>
    /// 党费
    /// </summary>
    public class PartyMemberCost : EntityBaseOperation
    {
        #region 属性

        /// <summary>
        /// 缴费党员
        /// </summary>
        public string LoginID { get; set; }

        /// <summary>
        /// 年
        /// </summary>
        public string Year { get; set; }

        /// <summary>
        /// 月
        /// </summary>
        public string Month { get; set; }

        /// <summary>
        /// 党费
        /// </summary>
        public decimal Cost { get; set; }
        /// <summary>
        /// 审核意见
        /// </summary>
        public string AduitContent { get; set; }
        /// <summary>
        /// 1草稿 2审核中 4通过 5待付款 8驳回
        /// </summary>
        public int Status { get; set; }
        /// <summary>
        /// 缴费日期
        /// </summary>
        public DateTime? PayDate { get; set; }

        /// <summary>
        /// 缴费方式（线下缴纳，线上缴纳） 
        /// </summary>
        public string PayWay { get; set; }

        /// <summary>
        /// 订单编号
        /// </summary>
        public string OrderNumber { get; set; }

        #endregion

        #region 

        /// <summary>
        /// 获取详细 
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="branchID"></param>
        /// <param name="year"></param>
        /// <param name="searchKey"></param>
        /// <returns></returns>
        public static DataSet GetBranchCost(int pageIndex, int pageSize, string branchID, int year, string searchKey = "")
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "pageIndex",Value = pageIndex},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "pageSize",Value = pageSize},
                new MySqlParameter(){DbType = DbType.String,ParameterName = "inBranchID",Value = branchID},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "inYear",Value = year},
                new MySqlParameter(){DbType = DbType.String,ParameterName = "SearchKey",Value = searchKey}
            };
            string sql = string.Format(@" call sp_Cost_GetBranchCost(?inYear,?pageIndex,?pageSize,?inBranchID,?SearchKey); ");
            return MySqlHelper.ExecuteDataSet(sql, parameters.ToArray());

        }

        /// <summary>
        /// 获取详细(统计) 
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="branchID"></param>
        /// <param name="year"></param>
        /// <param name="searchKey"></param>
        /// <returns></returns>
        public static DataSet GetBranchCostTotal(int pageIndex, int pageSize, string branchID, int year, string searchKey = "")
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "pageIndex",Value = pageIndex},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "pageSize",Value = pageSize},
                new MySqlParameter(){DbType = DbType.String,ParameterName = "inBranchID",Value = branchID},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "inYear",Value = year},
                new MySqlParameter(){DbType = DbType.String,ParameterName = "SearchKey",Value = searchKey}
            };
            string sql = string.Format(@" call sp_Cost_GetBranchCostTotal(?inYear,?pageIndex,?pageSize,?inBranchID,?SearchKey); ");
            return MySqlHelper.ExecuteDataSet(sql, parameters.ToArray());

        }

        /// <summary>
        /// 获取用户缴费信息
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="branchID"></param>
        /// <returns></returns>
        public static DataSet GetMemberCost(int pageIndex, int pageSize, string branchID)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.String,ParameterName = "branchID",Value = branchID}
            };
            StringBuilder sb = new StringBuilder();

            if (branchID == "")
            {
                sb.Append(@"select SQL_CALC_FOUND_ROWS  data.*,m.UserName from tblPartyMemberCost data 
                        left join tblpartyorgmember m on (m.Loginid=data.LoginID)
                        where m.branchID in (select ChildBranchID from tblpartyorgbranchrelation r)
                         ");
            }
            else
            {
                sb.Append(@"select SQL_CALC_FOUND_ROWS  data.*,m.UserName from tblPartyMemberCost data 
                        left join tblpartyorgmember m on (m.Loginid=data.LoginID)
                        where m.branchID in (select ChildBranchID from tblpartyorgbranchrelation r where r.BranchID=?branchID)
                         ");
            }

            sb.Append(" order by data.CreateTime desc ");

            if (pageIndex > -1)
            {
                sb.AppendFormat(" LIMIT {0} ,{1}; SELECT FOUND_ROWS(); ", pageIndex, pageSize);
            }
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());

        }

        /// <summary>
        /// 获取用户缴费信息
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="branchID"></param>
        /// <param name="username">姓名</param>
        /// <returns></returns>
        public static DataSet GetMemberCost(int pageIndex, int pageSize, string branchID, string username)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize},
                new MySqlParameter(){DbType = DbType.String,ParameterName = "branchID",Value = branchID}
            };
            StringBuilder sb = new StringBuilder();

            if (branchID == "")
            {
                sb.Append(@"select SQL_CALC_FOUND_ROWS  data.*,m.UserName from tblPartyMemberCost data 
                        left join tblpartyorgmember m on (m.Loginid=data.LoginID)
                        where m.branchID in (select ChildBranchID from tblpartyorgbranchrelation r)
                         ");
            }
            else
            {
                sb.Append(@"select SQL_CALC_FOUND_ROWS  data.*,m.UserName from tblPartyMemberCost data 
                        left join tblpartyorgmember m on (m.Loginid=data.LoginID)
                        where m.branchID in (select ChildBranchID from tblpartyorgbranchrelation r where r.BranchID=?branchID)
                         ");
            }

            if (!string.IsNullOrWhiteSpace(username))
            {
                sb.Append(string.Format(" and m.UserName like '%{0}%' ", username));
            }

            sb.Append(" order by data.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());

        }

        public static string GetMemberCostCount(string branchID)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.String,ParameterName = "branchID",Value = branchID}
            };
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select COUNT(*) as count from tblPartyMemberCost data 
                        left join tblpartyorgmember m on (m.Loginid=data.LoginID)
                        where m.branchID in (select ChildBranchID from tblpartyorgbranchrelation r where r.BranchID=?branchID)
                         ");
            return MySqlHelper.ExecuteScalar(sb.ToString(), parameters.ToArray()).ToString();
        }

        /// <summary>
        /// 根据订单编号获取数据
        /// </summary>
        /// <param name="orderNumber">订单编号</param>
        /// <returns></returns>
        public static DataTable GetMemberCostListByOrderNumber(string OrderNumber)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.String,ParameterName = "OrderNumber",Value = OrderNumber}
            };
            string sql = @" SELECT * FROM tblPartyMemberCost WHERE OrderNumber=@OrderNumber ";
            return MySqlHelper.ExecuteDataSet(sql, parameters.ToArray()).Tables[0];
        }
        #endregion
    }
}
